Course: Microsoft Power BI Training
Topic: Managing Data Source Credentials and Authentication
Course Link: Microsoft Power BI Training at JustAcademy
Demo Link: Register for a Course Demo
JustAcademy Website: Visit JustAcademy
Microsoft Power BI connects to various data sources to collect, transform, model, and visualize business information. These sources may include Excel files, SQL Server databases, SharePoint, cloud databases, web APIs, CSV files, and online services.
Many data sources require users or applications to prove their identity before they can access the data. This process is called authentication. The information used to establish that identity, such as a username and password, an access token, or an organizational sign-in, is associated with the data source credentials.
Managing data source credentials and authentication is essential for creating secure, reliable, and maintainable Power BI reports. Even if a report works correctly in Power BI Desktop, its published semantic model may fail to refresh in Power BI Service if credentials are missing, expired, or configured incorrectly.
Data source credentials are authentication details or authorization mechanisms that Power BI uses to access a connected data source.
For example, a company may store its sales information in a SQL Server database. Before Power BI can retrieve the sales records, it may need an authorized database account or an approved organizational identity.
Common examples of data source credentials include:
Authentication is the process of verifying the identity of a user, service, or application attempting to connect to a data source.
Power BI uses the authentication method supported by the source and connector. After authentication, the source or service determines what the authenticated identity is allowed to access.
Authorization determines which data, tables, files, or operations an authenticated identity is permitted to access.
Authentication and authorization are related but different concepts. Authentication verifies identity, while authorization determines permissions.
Example: A user may successfully sign in to a SQL Server database but still be unable to read a particular table because the account lacks the required database permissions.
Correct credential management helps Power BI access data securely and keeps reports working during scheduled and manual refresh operations.
Incorrect credentials may prevent Power BI from retrieving updated data. Depending on the source and connection type, users may encounter authentication errors, access-denied messages, failed refresh operations, or unavailable data.
For example, if a database password changes but Power BI continues using the old password, a scheduled refresh may fail until the stored credentials are updated.
Power BI supports different authentication methods depending on the data source, connector, gateway configuration, and connection scenario. The correct method must be selected for the particular source.
Basic authentication typically uses a username and password to authenticate with a supported service or data source.
Example: A supported database connector may allow a database username and password to be entered when establishing a connection.
Advantages:
Limitations:
Windows authentication uses a Windows identity to access supported data sources, commonly in an organizational network.
It is frequently used with supported on-premises database environments, such as SQL Server, when the account has the necessary permissions.
Example: A business analyst connects Power BI Desktop to an internal SQL Server using an approved Windows account.
Important considerations:
Organizational account authentication uses an approved work or school identity to access supported Microsoft and third-party services.
For example, a user may sign in with a Microsoft Entra ID account to access a supported cloud data source.
Benefits:
The exact sign-in flow and permissions depend on the source and the connector's authentication support.
OAuth 2.0 is an authorization framework commonly used by cloud applications and APIs. It allows an application to obtain access to protected resources through an approved authorization process.
Power BI connectors may use OAuth-based authentication to access supported services without requiring the user to provide their password directly to the connected application.
Example: A user connects to a supported online service and completes an organizational sign-in and consent process.
Important points:
Anonymous authentication is used when a data source permits access without a username or password through the selected connector.
This method may be suitable for publicly accessible resources or endpoints that explicitly permit anonymous requests.
Security consideration: Anonymous access should only be used when appropriate for the data source. A URL being accessible does not automatically mean that all data exposed by it is safe to access without authentication.
A service principal represents an application identity in Microsoft Entra ID. In supported Power BI and data-source scenarios, application identities can be used for automated or service-to-service access.
Such configurations may require administrator consent, API permissions, tenant settings, appropriate roles, and source-specific setup.
Service principals are not interchangeable with ordinary user credentials, and not every Power BI connector or refresh scenario supports them.
| Authentication Method | Typical Use | Important Consideration |
|---|---|---|
| Basic | Supported databases and services | Protect credentials and monitor password changes. |
| Windows | Supported on-premises data sources | Check domain, account, network, and gateway configuration. |
| Organizational account | Supported cloud and organizational services | Ensure the identity has access and satisfies organizational policies. |
| OAuth 2.0 | Supported cloud services and APIs | Manage consent, token expiration, and reauthentication. |
| Anonymous | Sources that explicitly permit unauthenticated access | Confirm that anonymous access is appropriate and secure. |
| Service principal | Supported application and automation scenarios | Requires suitable application permissions and source support. |
Power BI Desktop provides tools for creating and managing connections to data sources. During connection setup, the connector may ask you to select an authentication method and supply the required credentials.
Data Source Settings allows users to inspect and manage connection information and permissions for sources used in Power BI Desktop.
Power BI Desktop may provide options for managing sources used by the current file and global permissions. Check which scope is selected before making changes.
| Setting | Purpose | When to Use It |
|---|---|---|
| Current file or workbook permissions | Manages permissions associated with sources used by the current project. | When a particular report needs updated credentials or connection settings. |
| Global permissions | Manages stored permissions that may be reused for matching data sources across files. | When managing shared local connection permissions. |
The available options and labels can vary by Power BI Desktop version and connection type.
Credentials may need to be updated when a password changes, an account is replaced, access permissions are modified, or an authentication token needs renewal.
Sometimes stored credentials become invalid or conflict with the intended authentication method. Clearing the permissions for a particular source can allow Power BI to prompt for new credentials the next time a connection is made.
Warning: Clearing permissions can affect other reports or queries that rely on the same stored credentials, particularly when global permissions are involved. Avoid clearing all permissions unless you understand the consequences.
Power BI Service manages published semantic models, reports, connections, and refresh settings. Credentials configured in Power BI Desktop do not always automatically provide the authentication configuration required by the published semantic model.
After publishing, you may need to configure data source credentials or gateway connections in Power BI Service.
Cloud sources that Power BI Service can access directly may allow credentials to be configured without an on-premises gateway.
The exact settings depend on the connector, connection configuration, and permissions. Some connection types may use centrally managed connections rather than credentials configured directly on the semantic model.
Some credential-management operations require the user to own the semantic model. If you have the necessary permissions but do not own the model, the service may offer a Take over option.
Taking over a semantic model changes its ownership context and may affect the credentials or connections used for refresh. Before taking over a production model, check the organization's ownership and operational requirements.
On-premises data sources are hosted within a local network or organizational infrastructure. Power BI Service usually needs an appropriate data gateway when it cannot directly access these sources.
An on-premises data gateway is software that provides a secure connection between supported local data sources and Microsoft cloud services, including Power BI.
For example, a company may store its sales database on an internal SQL Server that is not directly accessible from the internet. A properly configured gateway can enable Power BI Service to reach the database for supported refresh and query scenarios.
If you are not a gateway administrator, contact the administrator to create or modify the required data source definition.
| Feature | Personal Mode Gateway | Standard On-Premises Gateway |
|---|---|---|
| Typical use | Individual users managing their own supported connections. | Organizational environments with shared data sources and administration. |
| Administration | Generally managed by the individual who configured it. | Can be managed by authorized gateway administrators. |
| Shared connections | More limited sharing and management capabilities. | Supports managed data source definitions and controlled user access. |
| Credentials | Configured in the supported semantic model settings. | Typically defined and managed for the gateway data source by its administrator. |
The supported data sources and features differ between gateway modes. Select the mode that matches the organization's security and operational requirements.
Not every data source requires a gateway. Power BI Service may connect directly to supported cloud sources when network access and authentication are configured correctly.
Examples include supported cloud databases, online services, and cloud-hosted files. Authentication may be configured through organizational sign-in, OAuth-based authorization, or another connector-supported method.
Examples include internal SQL Server databases, local file systems, and other supported services that cannot be accessed directly from Power BI Service.
| Factor | Cloud Connection | Gateway Connection |
|---|---|---|
| Network access | Power BI Service connects directly when supported. | Power BI Service communicates through the configured gateway. |
| Credential management | Often managed through semantic model or cloud connection settings. | Depends on gateway mode and the configured gateway data source. |
| Administration | Requires appropriate source and service permissions. | May require gateway administrator involvement. |
| Common issue | Expired sign-in or insufficient source permissions. | Gateway offline, incorrect mapping, or invalid source credentials. |
Scheduled refresh allows Power BI Service to retrieve updated data automatically. For a refresh to succeed, the service must be able to authenticate to every required data source.
When a refresh begins, Power BI uses the applicable source connection and authentication configuration to access the data. If the credentials are invalid, the connection may fail before the data can be retrieved.
A report that refreshes successfully in Power BI Desktop may fail in Power BI Service because the local environment and cloud service have different access paths, stored credentials, network permissions, or gateway requirements.
If a password used by a stored connection changes, Power BI may continue attempting to authenticate with the old password until the appropriate credential configuration is updated.
To resolve the issue, update the credentials in the correct location. Depending on the connection, this may be Power BI Desktop, semantic model settings, a gateway data source, or a centrally managed connection.
Power Query privacy levels help control how data from different sources can be combined. They are separate from authentication and do not replace the need for valid credentials.
| Privacy Level | Description | Example |
|---|---|---|
| Public | For data intended to be publicly accessible. | A public dataset or openly available information. |
| Organizational | For data intended to be shared within an organization. | An approved internal business reporting source. |
| Private | For confidential data that should be isolated from other sources during query combination. | A sensitive internal dataset. |
Choose a privacy level that reflects the actual sensitivity and intended use of the data. Do not mark a source as Public simply to bypass a query or refresh problem.
A single Power BI semantic model can use multiple data sources. For example, a sales dashboard may combine transaction data from SQL Server, product information from Excel, and targets from a SharePoint file.
Each source may require its own connection details and authentication method.
| Data Source | Example Authentication | Possible Connection Requirement |
|---|---|---|
| SQL Server database | Windows or database authentication, as supported | Gateway when the server is on-premises and inaccessible directly. |
| SharePoint Online | Organizational account or another supported method | Cloud connection, depending on the connector and configuration. |
| Excel file on a local drive | Source-specific file access and connection configuration | Gateway or an alternative supported hosting arrangement for service refresh. |
| Cloud database | Organizational, database, OAuth-based, or another supported method | Direct cloud connection when supported and accessible. |
The exact method must be confirmed for each connector. A source's location alone does not determine every authentication requirement.
Problem: Power BI cannot sign in to the data source.
Possible causes:
Solution: Verify the account, update the credentials in the correct configuration location, and test the connection again.
Problem: Authentication succeeds, but Power BI cannot read the required data.
Possible causes:
Solution: Ask the source administrator to verify the account's permissions and grant only the access necessary for the reporting task.
Problem: A cloud connection fails after previously working.
Possible causes:
Solution: Reauthenticate using the supported connector workflow, verify consent and account permissions, and test the connection.
Problem: Power BI Service cannot reach a source through the configured gateway.
Solution: Verify that the gateway machine is running, the gateway service is available, the required connection exists, and the configured account has access. Contact the gateway administrator if you do not have permission to manage it.
Problem: The report refreshes locally but fails after publication.
Possible causes:
Solution: Review semantic model settings, verify connection mappings and credentials, and check gateway status and refresh history.
Problem: A query combining different sources fails because of privacy restrictions or incompatible settings.
Solution: Review the privacy levels for each source and confirm that the data-combination design complies with organizational policies. Do not disable security protections without understanding the implications.
Problem: A report uses dynamically constructed data source locations that Power BI Service cannot refresh in the configured scenario.
Solution: Review the query design and connector documentation. Where appropriate, use a supported static connection pattern, supported parameters, or another architecture compatible with the service.
Data source credentials can provide access to confidential company information. Proper handling is therefore essential for secure Power BI development and administration.
Suppose a retail organization stores sales transactions in a SQL Server database. A Power BI developer needs to connect to the database, create a sales dashboard, and refresh the published report every day.
SELECT
OrderID,
OrderDate,
CustomerID,
ProductID,
Quantity,
SalesAmount
FROM dbo.Sales
WHERE OrderDate >= '2026-01-01';This query retrieves selected sales fields for records on or after January 1, 2026. The example illustrates data retrieval only; successful execution also depends on valid authentication and sufficient database permissions.
Power BI connects to the database using the configured authentication method and retrieves the permitted data. After publication, the service uses the applicable credentials and connection configuration to refresh the semantic model. If the password changes or gateway access fails, the developer or administrator investigates the issue and corrects the relevant settings.
Objective: Practice configuring and managing data source credentials in Power BI Desktop and Power BI Service.
Answer: Data source credentials are authentication details or authorization mechanisms that Power BI uses to access a connected data source.
Answer: Authentication verifies the identity of a user, application, or service attempting to access a resource.
Answer: Authorization determines which resources or operations an authenticated identity is permitted to access.
Answer: Depending on the connector, supported methods may include Basic, Windows, organizational account, OAuth-based authentication, Anonymous, or application identity methods.
Answer: Open Data Source Settings, select the required source, edit its permissions or credentials, choose the correct authentication method, provide the updated information, and test the connection.
Answer: Open the semantic model settings and update the applicable Data source credentials or connection configuration. If the source uses a managed gateway connection, the gateway administrator may need to update its credentials.
Answer: It is software that enables supported Microsoft cloud services to connect securely to data sources hosted within an organization's local network.
Answer: The stored credentials may still contain the old password. The appropriate connection or gateway configuration must be updated before refresh can authenticate successfully.
Answer: OAuth 2.0 is an authorization framework that allows applications to obtain access to protected resources through an approved authorization process.
Answer: A supported cloud connection can be accessed directly by Power BI Service, while a gateway connection routes access through a configured gateway for sources that need it.
Answer: Privacy levels help govern how data from different sources can be combined during query evaluation. They are separate from authentication credentials.
Answer: Hard-coded secrets can be exposed through source code, shared files, or other artifacts. Credentials should be managed through supported, secure connection and secret-management mechanisms.
Answer: Check service-side credentials, gateway requirements, source permissions, connection mappings, network accessibility, and refresh history.
Answer: Least privilege means granting a user, service, or application only the minimum permissions needed to perform its required tasks.
Managing data source credentials and authentication is a fundamental Power BI administration skill. Correct configuration allows Power BI Desktop and Power BI Service to access authorized data sources and helps ensure that refresh operations work as expected.
Understanding authentication methods, managing credentials, configuring gateway connections, and troubleshooting access failures helps developers build more secure and reliable reporting solutions. By following appropriate security practices and regularly reviewing connection settings, organizations can protect sensitive information while maintaining effective business intelligence workflows.
